﻿-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
USE [WarehouseManagement]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		AnhDQ
-- Create date: 3/16/2011
-- Description:	This stored procedure use to get an Employee record
-- =============================================
ALTER PROCEDURE [SearchEmployee]
(
@FullName NVARCHAR(50) = NULL, 
@DateOfBirthFrom DATE = NULL,
@DateOfBirthTo DATE = NULL,
@JoinedDateFrom DATE = NULL,
@JoinedDateTo DATE = NULL,
@RoleID INT = NULL,
@IsDelete BIT = 0
)
AS
BEGIN
	DECLARE @SearchEmployeeQuery NVARCHAR(MAX) = 
		'
		SELECT 
			e.EmployeeID
			, e.FullName
			, e.DateOfBirth
			, e.IdentityCardNumber
			, e.Email
			, e.Gender
			, e.JoinedDate
			, e.[Address]
			, e.MobileNumber
			, e.PhoneNumber
			, e.[Description]
			, e.UserName
			, e.IsDelete
			, et.EmploymentTypeID
			, et.EmploymentType
			, r.BriefDescription
			, r.RoleID
			, r.RoleName
			, r.IsDelete
		FROM 
			[Employee] e 
		INNER JOIN
			[EmploymentType] et
		ON
			e.EmploymentTypeID = et.EmploymentTypeID 
		INNER JOIN
			[Role] r
		ON
			e.RoleID = r.RoleID
		WHERE UserName <> ''admin'''
	
	-- Chuoi dieu kien IsDelete 
	DECLARE @IsDeleteConditionTemplate NVARCHAR(MAX) = 
		N'
			AND e.IsDelete = @IsDelete'
	DECLARE @IsDeletePlace NVARCHAR(MAX) = N'@IsDelete'
	DECLARE @IsDeleteCondition NVARCHAR(MAX) = ''
	
	IF (@IsDelete IS NOT NULL)
		SET @IsDeleteCondition = REPLACE ( @IsDeleteConditionTemplate , @IsDeletePlace , @IsDelete )

	-- Chuoi dieu kien FullName 
	DECLARE @FullNameConditionTemplate NVARCHAR(MAX) = 
		N'
			AND e.FullName LIKE ''%@FullName%'' '
	DECLARE @FullNamePlace NVARCHAR(MAX) = N'@FullName'
	DECLARE @FullNameCondition NVARCHAR(MAX) = ''

	IF (@FullName IS NOT NULL)
		SET @FullNameCondition = REPLACE ( @FullNameConditionTemplate , @FullNamePlace , @FullName )

	-- Chuoi dieu kien RoleID
	DECLARE @RoleIDConditionTemplate NVARCHAR(MAX) = 
		N'
			AND e.RoleID = @RoleID'
	DECLARE @RoleIDPlace NVARCHAR(MAX) = N'@RoleID'
	DECLARE @RoleIDCondition NVARCHAR(MAX) = ''

	IF (@RoleID IS NOT NULL)
		SET @RoleIDCondition = REPLACE ( @RoleIDConditionTemplate , @RoleIDPlace , @RoleID )

	-- Chuoi dieu kien DateOFBirthFrom
	DECLARE @DateOfBirthFromConditionTemplate NVARCHAR(MAX) = 
		N'
			AND e.DateOfBirth >= ''@DateOfBirthFrom'''
	DECLARE @DateOfBirthFromPlace NVARCHAR(MAX) = N'@DateOfBirthFrom'
	DECLARE @DateOfBirthFromCondition NVARCHAR(MAX) = ''

	IF (@DateOfBirthFrom IS NOT NULL)
		SET @DateOfBirthFromCondition = REPLACE ( @DateOfBirthFromConditionTemplate , @DateOfBirthFromPlace , @DateOfBirthFrom )

	-- Chuoi dieu kien DateOFBirthTo
	DECLARE @DateOfBirthToConditionTemplate NVARCHAR(MAX) = 
		N'
			AND	e.DateOfBirth <= ''@DateOfBirthTo'''
	DECLARE @DateOfBirthToPlace NVARCHAR(MAX) = N'@DateOfBirthTo'
	DECLARE @DateOfBirthToCondition NVARCHAR(MAX) = ''

	IF (@DateOfBirthTo IS NOT NULL)
		SET @DateOfBirthToCondition = REPLACE ( @DateOfBirthToConditionTemplate , @DateOfBirthToPlace , @DateOfBirthTo )

	-- Chuoi dieu kien JoinedDateFrom
	DECLARE @JoinedDateFromConditionTemplate NVARCHAR(MAX) = 
		N'
			AND e.JoinedDate >= ''@JoinedDateFrom'''
	DECLARE @JoinedDateFromPlace NVARCHAR(MAX) = N'@JoinedDateFrom'
	DECLARE @JoinedDateFromCondition NVARCHAR(MAX) = ''

	IF (@JoinedDateFrom IS NOT NULL)
		SET @JoinedDateFromCondition = REPLACE ( @JoinedDateFromConditionTemplate , @JoinedDateFromPlace , @JoinedDateFrom )

	-- Chuoi dieu kien JoinedDateTo
	DECLARE @JoinedDateToConditionTemplate NVARCHAR(MAX) = 
		N'
			AND e.JoinedDate <= ''@JoinedDateTo'''
	DECLARE @JoinedDateToPlace NVARCHAR(MAX) = N'@JoinedDateTo'
	DECLARE @JoinedDateTOCondition NVARCHAR(MAX) = ''

	IF (@JoinedDateTo IS NOT NULL)
		SET @JoinedDateToCondition = REPLACE ( @JoinedDateToConditionTemplate , @JoinedDateToPlace , @JoinedDateTo )

	-- Thuc hien query

	SELECT @SearchEmployeeQuery += @IsDeleteCondition + @FullNameCondition + @RoleIDCondition + @DateOfBirthFromCondition
					+ @DateOfBirthToCondition + @JoinedDateFromCondition + @JoinedDateToCondition

	PRINT @SearchEmployeeQuery

	EXEC sp_sqlexec @p1 = @SearchEmployeeQuery
	
		 
END

/*
USE WarehouseManagement
GO
select * from Employee
EXEC [SearchEmployee] @FullName = 'a'
*/